package com.demo;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class get {

    public static void main(String[] args) throws Exception {
        // 连接数据库
        Connection connection = DriverManager.getConnection("jdbc:sqlserver://192.168.30.41:1433;DatabaseName=MSAPUB_CBBG", "sa", "123@abc");
        Statement statement = connection.createStatement();

        // 读取Excel文件
        FileInputStream file = new FileInputStream(new File("E:\\work\\project_Doc\\otherDoc\\cj-xn60主程序V1.3.44前版本.xlsx"));
        Workbook workbook = new XSSFWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表

        for (int i = 1; i < 5892; i++) {
            Row row = sheet.getRow(i); // 获取第一行
            String mmsi = row.getCell(1).getStringCellValue();
            // 执行SQL查询，基于Excel中的数据
            String sql = "select DISTINCT  m.SHIP_NAME_CN,m.mmsi,s.MMSI,s.SHIP_NAME_CN,s.bid_name,s.mobile_phone \n" +
                    "from MSAPUB_CB.dbo.C_CB_SHIP_MMSI m,MSAPUB_CBBG.dbo.C_CB_SHIP_EEP_REPORT_REC s \n" +
                    "WHERE m.SHIP_NAME_CN=s.SHIP_NAME_CN\n" +
                    "and m.mmsi ='" + mmsi + "'" +
                    " ORDER BY m.MMSI desc  ";
            ResultSet resultSet = statement.executeQuery(sql);

            // 将查询结果填充回Excel的相应行
            while (resultSet.next()) {
                for (int j = 1; j <= resultSet.getMetaData().getColumnCount(); j++) {
                    Cell newCell = row.createCell(6); // 在当前行的第7列创建单元格
                    newCell.setCellValue(resultSet.getString("bid_name")); // 设置单元格值
                    Cell newCell7 = row.createCell(7);
                    newCell7.setCellValue(resultSet.getString("mobile_phone")); // 设置单元格值
                }
            /*Row newRow = sheet.createRow(row.getRowNum() + resultSet.getMetaData().getColumnCount()); // 在当前行下方创建新行
            for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
                Cell newCell = newRow.createCell(i - 1);
                newCell.setCellValue(resultSet.getString(i)); // 根据列索引设置单元格值
            }*/
            }

        }

        // 保存修改后的Excel文件
        FileOutputStream outFile = new FileOutputStream("E:\\work\\project_Doc\\otherDoc\\cj-xn60.xlsx");
        workbook.write(outFile);
        outFile.close();
        workbook.close();
    }
}
